package net.adetech.dcweb.core.dc.service.impl;

import net.adetech.dcweb.core.dc.entity.DcData;
import net.adetech.dcweb.core.dc.service.IDcDataService;
import org.dom4j.Document;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import java.io.ByteArrayInputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * Created by sunfeihu on 2016/10/20.
 */
@Service
public class DcDataServiceImpl implements IDcDataService {

    private final Logger logger = LoggerFactory.getLogger(DcDataServiceImpl.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void save(String xml) throws Exception {
        SAXReader saxReader = new SAXReader();
        Document document = saxReader.read(new ByteArrayInputStream(xml.getBytes("UTF-8")));
        List<Node> list = document.selectNodes("//Row");
        Node row = null;
        Node col = null;
        List<Node> list1 = null;
        DcData dcData = new DcData();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (int i = 2; i < list.size(); i++) {
            row = list.get(i);
            list1 = row.selectNodes("Data");
            for (int j = 0; j < list1.size(); j++) {
                col = list1.get(j);
                if (j == 0) {//班次
                    dcData.setNumber(col.getText().trim());
                } else if (j == 1) {//舱位
                    dcData.setSpace(col.getText().trim());
                } else if (j == 2) {//更新时间
                    dcData.setUpdateTime(sdf.parse(col.getText().trim()));
                } else if (j == 3) {//检票时间
                    dcData.setHaveTime(sdf.parse(col.getText().trim()));
                } else if (j == 4) {//主键
                    dcData.setKey(col.getText().trim());
                } else if (j == 5) {//开船时间
                    dcData.setLeaveTime(sdf.parse(col.getText().trim()));
                } else if (j == 6) {//类型
                    dcData.setType(col.getText().trim());
                } else if (j == 7) {//起止地
                    dcData.setStartAndEnd(col.getText().trim());
                } else if (j == 8) {//入库时间
                    dcData.setInDate(sdf.parse(col.getText().trim()));
                } else if (j == 9) {//身份证号
                    dcData.setCardNo(col.getText().trim());
                } else if (j == 10) {//售票时间
                    dcData.setBuyTime(sdf.parse(col.getText().trim()));
                } else if (j == 11) {//姓名
                    dcData.setRealName(col.getText().trim());
                }
            }
            dcData.setCardType("身份证");
            DcData mydcdata = getByKey(dcData.getKey());
            //如果存在，是修改
            if (null != mydcdata) {
                dcData.setId(mydcdata.getId());
                this.update(dcData);
            } else {//否则，是新增
                this.save(dcData);
            }
        }
    }

    private DcData getByKey(String key) {
        final String sql = " select * from dc_data where `key` = ? ";
        List<DcData> list = jdbcTemplate.query(sql, new Object[]{key}, new DcDataRowerMapper());
        if (null != list && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public DcData save(DcData entity) {
        final String sql = " insert into dc_data (uuid,add_ip,add_info,add_user,add_date,update_ip,update_info,update_user,update_date,number,real_name,card_type,card_no,leave_time,space,start_and_end,buy_time,have_time,`key`) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        final DcData finalEntity = entity;
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
                ps.setObject(1, finalEntity.getUuid());
                ps.setObject(2, finalEntity.getAddIp());
                ps.setObject(3, finalEntity.getAddInfo());
                ps.setObject(4, finalEntity.getAddUser());
                if (null != finalEntity.getAddDate()) {
                    ps.setObject(5, new java.sql.Timestamp(finalEntity.getAddDate().getTime()));
                } else {
                    ps.setObject(5, null);
                }
                ps.setObject(6, finalEntity.getUpdateIp());
                ps.setObject(7, finalEntity.getUpdateInfo());
                ps.setObject(8, finalEntity.getUpdateUser());
                if (null != finalEntity.getUpdateDate()) {
                    ps.setObject(9, new java.sql.Timestamp(finalEntity.getUpdateDate().getTime()));
                } else {
                    ps.setObject(9, null);
                }
                ps.setObject(10, finalEntity.getNumber());
                ps.setObject(11, finalEntity.getRealName());
                ps.setObject(12, finalEntity.getCardType());
                ps.setObject(13, finalEntity.getCardNo());
                if (null != finalEntity.getLeaveTime()) {
                    ps.setObject(14, new java.sql.Timestamp(finalEntity.getLeaveTime().getTime()));
                } else {
                    ps.setObject(14, null);
                }
                ps.setObject(15, finalEntity.getSpace());
                ps.setObject(16, finalEntity.getStartAndEnd());
                if (null != finalEntity.getBuyTime()) {
                    ps.setObject(17, new java.sql.Timestamp(finalEntity.getBuyTime().getTime()));
                } else {
                    ps.setObject(17, null);
                }
                if (null != finalEntity.getHaveTime()) {
                    ps.setObject(18, new java.sql.Timestamp(finalEntity.getHaveTime().getTime()));
                } else {
                    ps.setObject(18, null);
                }
                ps.setObject(19, finalEntity.getKey());
                return ps;
            }
        }, keyHolder);
        Long id = keyHolder.getKey().longValue();
        return this.find(id);
    }


    @Override
    public int update(DcData entity) {
        final String sql = " update dc_data set add_info=?,update_info=?,number=?,real_name=?,card_type=?,card_no=?,leave_time=?,space=?,start_and_end=?,buy_time=?,have_time=?,`key`=? where id = ?";
        return jdbcTemplate.update(sql, new Object[]{
                entity.getAddInfo(),
                entity.getUpdateInfo(),
                entity.getNumber(),
                entity.getRealName(),
                entity.getCardType(),
                entity.getCardNo(),
                entity.getLeaveTime(),
                entity.getSpace(),
                entity.getStartAndEnd(),
                entity.getBuyTime(),
                entity.getHaveTime(),
                entity.getKey(),
                entity.getId()
        });
    }

    @Override
    public DcData find(Long id) {
        final String sql = " select * from dc_data where id = ? ";
        try {
            return jdbcTemplate.queryForObject(sql, new Object[]{id}, new DcDataRowerMapper());
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public DcData findByKey(String key) {
        final String sql = " select * from dc_data where `key` = ?";
        try {
            List<DcData> dcDataList = jdbcTemplate.query(sql, new Object[]{key}, new DcDataRowerMapper());
            if (dcDataList!=null&&dcDataList.size()>0)
            {
                return dcDataList.get(0);
            }
            else {
                return null;
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }
}


class DcDataRowerMapper implements RowMapper<DcData> {
    @Override
    public DcData mapRow(ResultSet rs, int rowNum) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        Set
                <String> fieldSet = new HashSet
                <String>();
        for (int i = 1; i <= columnCount; i++) {
            fieldSet.add(rsmd.getColumnLabel(i));
        }
        DcData entity = new DcData();
        if (fieldSet.contains("leave_time") && null !=
                rs.getObject("leave_time")) {
            entity.setLeaveTime(new
                    java.util.Date(rs.getTimestamp("leave_time").getTime()));
        }
        if (fieldSet.contains("add_date") && null !=
                rs.getObject("add_date")) {
            entity.setAddDate(new
                    java.util.Date(rs.getTimestamp("add_date").getTime()));
        }
        if (fieldSet.contains("photo") && null !=
                rs.getObject("photo")) {
            entity.setPhoto(rs.getString
                    ("photo"));
        }
        if (fieldSet.contains("update_info") && null !=
                rs.getObject("update_info")) {
            entity.setUpdateInfo(rs.getString
                    ("update_info"));
        }
        if (fieldSet.contains("real_name") && null !=
                rs.getObject("real_name")) {
            entity.setRealName(rs.getString
                    ("real_name"));
        }
        if (fieldSet.contains("update_ip") && null !=
                rs.getObject("update_ip")) {
            entity.setUpdateIp(rs.getString
                    ("update_ip"));
        }
        if (fieldSet.contains("card_type") && null !=
                rs.getObject("card_type")) {
            entity.setCardType(rs.getString
                    ("card_type"));
        }
        if (fieldSet.contains("have_time") && null !=
                rs.getObject("have_time")) {
            entity.setHaveTime(new
                    java.util.Date(rs.getTimestamp("have_time").getTime()));
        }
        if (fieldSet.contains("alarm_date") && null !=
                rs.getObject("alarm_date")) {
            entity.setAlarmDate(new
                    java.util.Date(rs.getTimestamp("alarm_date").getTime()));
        }
        if (fieldSet.contains("uuid") && null !=
                rs.getObject("uuid")) {
            entity.setUuid(rs.getString
                    ("uuid"));
        }
        if (fieldSet.contains("update_date") && null !=
                rs.getObject("update_date")) {
            entity.setUpdateDate(new
                    java.util.Date(rs.getTimestamp("update_date").getTime()));
        }
        if (fieldSet.contains("space") && null !=
                rs.getObject("space")) {
            entity.setSpace(rs.getString
                    ("space"));
        }
        if (fieldSet.contains("number") && null !=
                rs.getObject("number")) {
            entity.setNumber(rs.getString
                    ("number"));
        }
        if (fieldSet.contains("buy_time") && null !=
                rs.getObject("buy_time")) {
            entity.setBuyTime(new
                    java.util.Date(rs.getTimestamp("buy_time").getTime()));
        }
        if (fieldSet.contains("add_ip") && null !=
                rs.getObject("add_ip")) {
            entity.setAddIp(rs.getString
                    ("add_ip"));
        }
        if (fieldSet.contains("update_user") && null !=
                rs.getObject("update_user")) {
            entity.setUpdateUser(rs.getString
                    ("update_user"));
        }
        if (fieldSet.contains("card_no") && null !=
                rs.getObject("card_no")) {
            entity.setCardNo(rs.getString
                    ("card_no"));
        }
        if (fieldSet.contains("id") && null !=
                rs.getObject("id")) {
            entity.setId(rs.getLong
                    ("id"));
        }
        if (fieldSet.contains("alarm_id") && null !=
                rs.getObject("alarm_id")) {
            entity.setAlarmId(rs.getLong
                    ("alarm_id"));
        }
        if (fieldSet.contains("add_info") && null !=
                rs.getObject("add_info")) {
            entity.setAddInfo(rs.getString
                    ("add_info"));
        }
        if (fieldSet.contains("add_user") && null !=
                rs.getObject("add_user")) {
            entity.setAddUser(rs.getString
                    ("add_user"));
        }
        if (fieldSet.contains("start_and_end") && null !=
                rs.getObject("start_and_end")) {
            entity.setStartAndEnd(rs.getString
                    ("start_and_end"));
        }
        if (fieldSet.contains("is_delete") && null !=
                rs.getObject("is_delete")) {
            entity.setIsDelete(
                    rs.getBoolean
                            ("is_delete"));
        }
        if (fieldSet.contains("key") && null !=
                rs.getObject("key")) {
            entity.setKey(rs.getString
                    ("key"));
        }
        if (fieldSet.contains("type") && null !=
                rs.getObject("type")) {
            entity.setType(rs.getString
                    ("type"));
        }
        if (fieldSet.contains("update_time") && null !=
                rs.getObject("update_time")) {
            entity.setUpdateTime(new
                    java.util.Date(rs.getTimestamp("update_time").getTime()));
        }
        if (fieldSet.contains("in_date") && null !=
                rs.getObject("in_date")) {
            entity.setInDate(new
                    java.util.Date(rs.getTimestamp("in_date").getTime()));
        }
        if (fieldSet.contains("man_type") && null !=
                rs.getObject("man_type")) {
            entity.setManType(rs.getLong("man_type"));
        }

        return entity;
    }
}